Import modules

import plotly.express as px
from postgresql_tools import load_query_df, create_db_conn

Retrieve data from SQL database

# Create connection to PostgreSQL database
conn = create_db_conn('config.ini')

Compare member and casual volume over time

# Load the member versus casual data into a dataframe
df_members = load_query_df('./sql/member_casual_volume.sql', conn, ['month_year', 'member_casual', 'trip_count'])

# Create a line chart
fig = px.line(df_members, x='month_year', y='trip_count', color='member_casual')

# Show the line chart in the Jupyter notebook
fig.show()

Member and casual trip volume is roughly the same over time

# Get rides by day of the week
df_members_dow = load_query_df('./sql/member_casual_volume_dow.sql', conn, ['day_of_week',
                                                                      'member_casual',
                                                                      'total_trips'])

day_of_week_names = {0: 'Sunday', 1: 'Monday', 2: 'Tuesday',
                     3: 'Wednesday', 4: 'Thursday', 5: 'Friday', 6: 'Saturday'}
df_members_dow['day_of_week_name'] = df_members_dow['day_of_week'].map(day_of_week_names)

fig = px.bar(df_members_dow,
             x='day_of_week_name',
             y='total_trips',
             color='member_casual',
             facet_col='member_casual')
fig.update_xaxes(title='Day of the week')
fig.show()

Casual members use the bikes more on the weekend. Member take many more trips during the week. Presumably this is due to commuting to work.

df_members_dow_duration = load_query_df('./sql/member_casual_duration.sql', conn, ['day_of_week',
                                                                                   'member_casual',
                                                                                   'average_duration'])

df_members_dow_duration['day_of_week_name'] = df_members_dow_duration['day_of_week'].map(day_of_week_names)
df_members_dow_duration['average_duration_minutes'] = df_members_dow_duration['average_duration'] / 60

fig = px.bar(df_members_dow_duration,
             x='day_of_week_name',
             y='average_duration_minutes',
             color='member_casual',
             facet_col='member_casual',
             category_orders={'member_casual': list(df_members_dow_duration['member_casual'].unique())})
fig.update_xaxes(title='Day of the week')

fig.show()

Casuals ride for much longer than members per trip, almost double.

df_members_dow_total_duration = load_query_df('./sql/member_casual_dow_duration.sql',
                                              conn, ['day_of_week',
                                                     'member_casual',
                                                     'total_duration'])

df_members_dow_total_duration['day_of_week_name'] = df_members_dow_total_duration['day_of_week'].map(day_of_week_names)
df_members_dow_total_duration['total_duration_days'] = df_members_dow_total_duration['total_duration'] / 86400

fig = px.bar(df_members_dow_total_duration,
             x='day_of_week_name',
             y='total_duration_days',
             color='member_casual',
             facet_col='member_casual',
             category_orders={'member_casual': list(df_members_dow_total_duration['member_casual'].unique())})
fig.update_xaxes(title='Day of the week')

fig.show()

Looking at total duration shows much more divergence in weekend usage.

df_members_at_risk_trips = load_query_df('./sql/at_risk_trips.sql',
                                         conn,
                                         ['day_of_week','member_casual','at_risk_trips'])

df_members_at_risk_trips['day_of_week_name'] = df_members_at_risk_trips['day_of_week'].map(day_of_week_names)

fig = px.bar(df_members_at_risk_trips,
             x='day_of_week_name',
             y='at_risk_trips',
             color='member_casual',
             facet_col='member_casual',
             category_orders={'member_casual': list(df_members_at_risk_trips['member_casual'].unique())})
fig.update_xaxes(title='Day of the week')

fig.show()

We have to send someone to track down bikes out for more than 1 day. We have to chase down far fewer member bike trips. This is yet another reason to push membership as the members take better care of the bikes.

# Load the heatmap data into a dataframe
df_trips = load_query_df('./sql/heatmap_casual.sql', conn, ['start_date',
                                                            'latitude',
                                                            'longitude',
                                                            'trip_count'])

# Create the heat map to show where to place ads for memberships
fig = px.density_mapbox(df_trips, lat='latitude', lon='longitude', z='trip_count',
                        radius=10,
                        center=dict(lat=41.9, lon=-87.6), zoom=10,
                        mapbox_style='open-street-map', animation_frame=df_trips['start_date'],
                        height=800)

# Show the heat map in the Jupyter notebook
fig.show()

The heatmap above shows casual bike trips taken per day since the start of 2022. Downtown, along US 41 and the Kennedy Expressway and the key places to place ads to reach casual members.

conn.close()